from typing import List

from common.date_util import DateUtil
from common.response import Response
from db.aiomysql_connector import AioMysqlClient
from header.digit_twin_header import DigitTwinHeader
from header.pm_header import PMHeader
from logger_config import logger
from services.cache_service import CacheUtils


class ProductionService(object):
    table = 'Production'
    OP10 = 'data105'
    OP20 = 'data106'
    OP30 = 'data107'
    OP40 = 'data108'
    OP50 = 'data109'
    op_code_list = ['data105',
                    'data106',
                    'data107',
                    'data108',
                    'data109'
                    ]
    # 有下线时间的作为一条产量,因此产量时间聚合按照下线时间聚合
    offline_code = 'data110'
    online_code = 'data104'
    # 当日每个时间段的参数
    start_hour = 8
    end_hour = 18
    hour_interval = 2

    def __init__(self):
        self.sql_client: AioMysqlClient = CacheUtils.get('aiomysql_pool')

    async def get_pass_rate(self):
        # 有下线时间的为完整一条数据
        try:
            all_sql = f"SELECT COUNT(*) as count FROM {self.table} WHERE {self.offline_code} IS NOT NULL"
            pass_sql = f"SELECT COUNT(*) as count FROM {self.table} WHERE {self.OP10} = 1 AND {self.OP20} = 1 AND {self.OP30} = 1 AND {self.OP40} = 1 AND {self.OP50} = 1"
            all_num = await self.sql_client.fetch_one(all_sql)
            pass_num = await self.sql_client.fetch_one(pass_sql)
            pass_rate = 0
            if all_num and pass_num:
                pass_rate = round(pass_num['count'] / all_num['count'], 2) if all_num['count'] else 0
        except Exception as e:
            pass_rate = 0
            logger.error(e)
        return {
            'rate': {
                'unit': '%',
                'rate': pass_rate * 100,
                'max': 100,
            },
            'max': 100,
        }

    async def get_ten_10_total_quality(self):
        ten_date, ten_date_str = await DateUtil.get_recent_day_10()
        # 近10日的产品合格信息（OP10-OP50均合格，则当前产品为合格品）
        # 总产量sql
        try:
            total_sql = f"SELECT DATE({self.offline_code}) AS date,COUNT(*) as count FROM {self.table} WHERE {self.offline_code} is not null and {self.offline_code} >= DATE(DATE_SUB(NOW(), INTERVAL 9 DAY))"
            complete_sql = total_sql + f" GROUP BY DATE({self.offline_code})"
            total_data: List[dict] = await self.sql_client.fetch_all(complete_sql)
            date_output_dict = {}  # 每一天的产量的dict
            if total_data:
                for d in total_data:
                    date_output_dict[d['date']] = {'count': d['count']}
            #
            # 良品率
            pass_sql = (
                f"SELECT DATE({self.offline_code}) AS date,COUNT(*) as count FROM {self.table} WHERE {self.OP10} = 1 AND {self.OP20} = 1 AND {self.OP30} = 1 AND {self.OP40} = 1 AND {self.OP50} = 1 "
                f"and data110 is not null and {self.offline_code} >= DATE(DATE_SUB(NOW(), INTERVAL 9 DAY)) GROUP BY DATE({self.offline_code})")
            pass_data: List[dict] = await self.sql_client.fetch_all(pass_sql)
            date_pass_dict = {}  # 每一天的产量的dict
            if pass_data:
                for d in pass_data:
                    date_pass_dict[d['date']] = {'count': d['count']}

            x_data, y_data = [], []
            for d in ten_date:
                if date_output_dict.get(d):
                    x_data.append(date_output_dict[d]['count'])
                else:
                    x_data.append(0)
                if date_pass_dict.get(d):
                    y_data.append(date_pass_dict[d]['count'])
                else:
                    y_data.append(0)
            y_data = [round(y / x, 2) * 100 if x else 0 for x, y in zip(x_data, y_data)]
        except:
            x_data, y_data = [], []

        return {
            'x': ten_date_str,
            'y': [{'name': '产量', 'data': x_data}, {'name': '良品率', 'data': y_data}]
        }

    async def cur_day_quality_monitor(self):
        x_data = DigitTwinHeader.station_list
        try:
            sql = f"SELECT * FROM {self.table} WHERE {self.offline_code} IS NOT NULL and DATE({self.offline_code}) = CURDATE() "
            station_code_list = DigitTwinHeader.station_code_list
            station_pass_num: dict = {code: 0 for code in station_code_list}
            today_data_list = await self.sql_client.fetch_all(sql)
            all_num = len(today_data_list) if today_data_list else 0
            if today_data_list:
                for d in today_data_list:
                    for s in station_code_list:
                        if d[s] == '1':
                            station_pass_num[s] += 1
            y_data = [round(value / all_num, 2) * 100 if all_num else 0 for value in station_pass_num.values()]
        except Exception as e:
            all_num = 0
            y_data = []
        return {
            'all_num': all_num,
            'x': x_data,
            'y': y_data
        }

    async def day_product_trend(self):
        try:
            sql = f"SELECT HOUR({self.offline_code}) as hour, COUNT(*) as count FROM {self.table} WHERE {self.offline_code} IS NOT NULL and DATE({self.offline_code}) = CURDATE() "
            sql = sql + f"GROUP BY HOUR({self.offline_code}) ORDER BY HOUR({self.offline_code})"
            hour_count_dict_list = await self.sql_client.fetch_all(sql)
            hour_count_dict = {d['hour']: d['count'] for d in hour_count_dict_list} if hour_count_dict_list else {}  # {小时:产量}
            h_list = [i for i in range(self.start_hour, self.end_hour + 1, self.hour_interval)]
            x_data = []
            y_data = []
            for h in h_list:
                hour_phrase_h = [i for i in range(h, h + self.hour_interval)]
                h_sum_count = 0
                for h_ in hour_phrase_h:
                    if hour_count_dict.get(h_):
                        h_sum_count += hour_count_dict[h_]
                y_data.append(h_sum_count)
                x_data.append(str(h).zfill(2) + ":00")
        except:
            x_data = []
            y_data = []


        return_data = {
            'x': x_data,
            'y': y_data,
        }
        return return_data

    async def production_query(self, param):
        serial_number, start_time, end_time = param.get('serial_number', ''), param.get('start_time', ''), param.get(
            'end_time', '')
        sql = f"SELECT * FROM {self.table}"
        if serial_number:
            sql = sql + " WHERE data100 = '{}'".format(serial_number)
            if start_time and end_time:
                sql = sql + " AND data104>='{}' and data104<='{}'".format(start_time, end_time)
        else:
            if start_time and end_time:
                sql = sql + " WHERE data104>='{}' and data104<='{}'".format(start_time, end_time)
        data = await self.sql_client.fetch_all(sql)
        header = PMHeader.production_header
        return {
            'header': header,
            'data': data,
        }
